﻿using Microsoft.AspNetCore.Http;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System;
using NPOI.SS.UserModel;
using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
//using NPOI.OpenXml4Net.OPC.Internal;
using System.Web;

namespace AuthorityManagement.Helper
{
    public class ToExcel
    {

        //private static string MapPath(string strPath)
        //{
        //    if (HttpContext.Current != null)
        //    {
        //        return HttpContext.Current.Server.MapPath(strPath);
        //    }
        //    else //非web程序引用             
        //    {
        //        strPath = strPath.Replace("/", "\\").Replace("~", "");
        //        if (strPath.StartsWith("\\"))
        //        {
        //            strPath = strPath.TrimStart('\\');
        //        }
        //        return System.IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, strPath);
        //    }
        //}

        /// <summary>
        /// 导出
        /// </summary>
        /// <param name="dt">数据原</param>
        /// <param name="ExcelSheetName">sheet名称</param>
        /// <param name="FileName">文件名</param>
        /// <param name="Employee_Code">目录名</param>
        public static void ExcelExport(DataTable dt, string ExcelSheetName, string FileName, string DirectoryName)
        {
            if (dt.Rows.Count > 0)
            {
                int RowCount = dt.Rows.Count;
                int SheetMaxCount = 65535;//65535 sheet最大数据行数
                int rows = 0;
                double sheetPage = Convert.ToDouble(Convert.ToDouble(RowCount) / Convert.ToDouble(SheetMaxCount));
                int sheetsCount = Convert.ToInt32(Math.Ceiling(sheetPage));
                IWorkbook hssfworkbook = new HSSFWorkbook();
                IDataFormat format = hssfworkbook.CreateDataFormat();
                for (int k = 0; k < sheetsCount; k++)
                {
                    string SheetName = ExcelSheetName + (k + 1).ToString();
                    hssfworkbook.CreateSheet(SheetName);
                    #region 创建样式对象
                    IFont Font_Size = hssfworkbook.CreateFont();
                    Font_Size.FontHeightInPoints = 10;

                    ICellStyle Style_Font_Size = hssfworkbook.CreateCellStyle();
                    Style_Font_Size.SetFont(Font_Size);

                    ICellStyle Style_Title = hssfworkbook.CreateCellStyle();
                    Style_Title.FillForegroundColor = HSSFColor.RoyalBlue.Index;
                    //Style_Title.FillPattern = FillPatternType.SQUARES;
                    Style_Title.FillBackgroundColor = HSSFColor.RoyalBlue.Index;

                    IFont Font_Size_Title = hssfworkbook.CreateFont();
                    Font_Size_Title.FontHeightInPoints = 11;
                    Font_Size_Title.Boldweight = (short)FontBoldWeight.Bold;
                    Font_Size_Title.Color = HSSFColor.White.Index;
                    Style_Title.SetFont(Font_Size_Title);

                    #endregion

                    #region 创建Excel列头
                    if (dt != null && dt.Rows.Count > 0)
                    {
                        hssfworkbook.GetSheet(SheetName).CreateRow(0);
                        for (int a = 0; a < dt.Columns.Count; a++)
                        {
                            hssfworkbook.GetSheet(SheetName).GetRow(0).CreateCell(a).CellStyle = Style_Title;
                            hssfworkbook.GetSheet(SheetName).GetRow(0).GetCell(a).SetCellValue(dt.Columns[a].ColumnName);
                            hssfworkbook.GetSheet(SheetName).SetColumnWidth(a, 20 * 256);
                        }
                    }
                    #endregion

                    #region 内容填空
                    if (dt != null && dt.Rows.Count > 0)
                    {
                        List<string> ToatalList = new List<string>();
                        int counts = 0;

                        for (int i = rows; i < dt.Rows.Count; i++)
                        {
                            if (k == 0 && i > SheetMaxCount - 1)
                            {
                                rows = SheetMaxCount;
                                break;
                            }
                            else if (k > 0 && i > (SheetMaxCount * (k + 1)) - 1)
                            {
                                rows = SheetMaxCount * (k + 1);
                                break;
                            }
                            hssfworkbook.GetSheet(SheetName).CreateRow(counts + 1);
                            for (int j = 0; j < dt.Columns.Count; j++)
                            {
                                hssfworkbook.GetSheet(SheetName).GetRow(counts + 1).CreateCell(j).CellStyle = Style_Font_Size;
                                hssfworkbook.GetSheet(SheetName).GetRow(counts + 1).GetCell(j).SetCellValue(dt.Rows[i][j].ToString());

                            }
                            counts++;
                        }
                    }
                    #endregion
                }
                string SaveFilePath = @"~/TemporaryFiles/" + DirectoryName + @"/"; /*HttpContext.Current.Server.MapPath("~/TemporaryFiles/" + DirectoryName + "/");*/
                if (!Directory.Exists(SaveFilePath))//是否存在该路径,不存在创建
                {
                    Directory.CreateDirectory(SaveFilePath);//创建
                }
                #region 删除Excel文件
                FileHelper file = new FileHelper();
                file.DeleteFile(@"~/TemporaryFiles/" + DirectoryName + @"/" + FileName);//HttpContext.Current.Server.MapPath("~/TemporaryFiles/" + DirectoryName + "/" + FileName));
                #endregion
                FileStream files = new FileStream(SaveFilePath + FileName, FileMode.Create);
                hssfworkbook.Write(files);
                files.Close();
                //HttpResponse contextResponse = HttpContext.Current.Response;
                //contextResponse.Redirect(string.Format("~/TemporaryFiles/" + DirectoryName + "/{0}", FileName + ".xls"), false);
            }
        }

        /// <summary>
        /// 生成导出文件
        /// </summary>
        /// <param name="dt">导出数据</param>
        /// <param name="ExcelSheetName">工作表名称</param>
        /// <param name="FileName">文件名（带后缀）</param>
        /// <param name="DirectoryName">保存目录名称</param>
        /// <param name="Operator">操作人（当前登录人帐号）</param>
        /// <param name="Theme">主题（备用，默认值：default）</param>
        /// <returns></returns>
        public static string ExcelExport(DataTable dt, string ExcelSheetName, string FileName, string DirectoryName, string Operator, string Theme)
        {
            if (dt.Rows.Count > 0)
            {
                int RowCount = dt.Rows.Count;
                int SheetMaxCount = 65535;//65535 sheet最大数据行数
                int rows = 0;
                double sheetPage = Convert.ToDouble(Convert.ToDouble(RowCount) / Convert.ToDouble(SheetMaxCount));
                int sheetsCount = Convert.ToInt32(Math.Ceiling(sheetPage));
                IWorkbook hssfworkbook = new HSSFWorkbook();
                IDataFormat format = hssfworkbook.CreateDataFormat();
                for (int k = 0; k < sheetsCount; k++)
                {
                    string SheetName = ExcelSheetName + (k + 1).ToString();
                    hssfworkbook.CreateSheet(SheetName);
                    #region 创建样式对象
                    IFont Font_Size = hssfworkbook.CreateFont();
                    Font_Size.FontHeightInPoints = 10;

                    ICellStyle Style_Font_Size = hssfworkbook.CreateCellStyle();
                    Style_Font_Size.SetFont(Font_Size);

                    ICellStyle Style_Title = hssfworkbook.CreateCellStyle();
                    Style_Title.FillForegroundColor = HSSFColor.RoyalBlue.Index;
                    Style_Title.FillPattern = FillPattern.Squares;
                    Style_Title.FillBackgroundColor = HSSFColor.RoyalBlue.Index;

                    IFont Font_Size_Title = hssfworkbook.CreateFont();
                    Font_Size_Title.FontHeightInPoints = 11;
                    Font_Size_Title.Boldweight = (short)FontBoldWeight.Bold;
                    Font_Size_Title.Color = HSSFColor.White.Index;
                    Style_Title.SetFont(Font_Size_Title);

                    #endregion

                    #region 创建Excel列头
                    if (dt != null && dt.Rows.Count > 0)
                    {
                        hssfworkbook.GetSheet(SheetName).CreateRow(0);
                        for (int a = 0; a < dt.Columns.Count; a++)
                        {
                            hssfworkbook.GetSheet(SheetName).GetRow(0).CreateCell(a).CellStyle = Style_Title;
                            hssfworkbook.GetSheet(SheetName).GetRow(0).GetCell(a).SetCellValue(dt.Columns[a].ColumnName);
                            hssfworkbook.GetSheet(SheetName).SetColumnWidth(a, 20 * 256);
                        }
                    }
                    #endregion

                    #region 内容填空
                    if (dt != null && dt.Rows.Count > 0)
                    {
                        List<string> ToatalList = new List<string>();
                        int counts = 0;

                        for (int i = rows; i < dt.Rows.Count; i++)
                        {
                            if (k == 0 && i > SheetMaxCount - 1)
                            {
                                rows = SheetMaxCount;
                                break;
                            }
                            else if (k > 0 && i > (SheetMaxCount * (k + 1)) - 1)
                            {
                                rows = SheetMaxCount * (k + 1);
                                break;
                            }
                            hssfworkbook.GetSheet(SheetName).CreateRow(counts + 1);
                            for (int j = 0; j < dt.Columns.Count; j++)
                            {
                                hssfworkbook.GetSheet(SheetName).GetRow(counts + 1).CreateCell(j).CellStyle = Style_Font_Size;
                                hssfworkbook.GetSheet(SheetName).GetRow(counts + 1).GetCell(j).SetCellValue(dt.Rows[i][j].ToString());

                            }
                            counts++;
                        }
                    }
                    #endregion
                }
                string SaveFilePath = "D:/qxAPI/Test/ExportFile/";
                if (!Directory.Exists(SaveFilePath))//是否存在该路径,不存在创建
                {
                    Directory.CreateDirectory(SaveFilePath);//创建
                }
                #region 删除Excel文件
                FileHelper file = new FileHelper();
                file.DeleteFile(SaveFilePath + FileName);
                #endregion
                FileStream files = new FileStream(SaveFilePath + FileName, FileMode.Create);
                hssfworkbook.Write(files);
                files.Close();
                return "D:/qxAPI/Test/ExportFile/" + FileName;
                //return DESEncrypt.Encrypt("~/TemporaryFiles/" + DirectoryName + "/" + Operator + "/" + FileName);

            }
            else
            {
                return "";
            }
        }

        /// <summary>
        /// 读取导入文件获取数据
        /// </summary>
        /// <param name="fileData"></param>
        /// <param name="cols"></param>
        /// <returns></returns>
        //public static DataTable ExportData(HttpFileCollection fileData)
        //{
        //    DataTable dt = null;

        //    #region  保存到临时目录中
        //    string excelFile = AppDomain.CurrentDomain.BaseDirectory + "TemporaryFiles\\Temp\\";

        //    if (!Directory.Exists(excelFile))//是否存在该路径,不存在创建
        //    {
        //        Directory.CreateDirectory(excelFile);//创建
        //    }
        //    excelFile = Path.Combine(excelFile, fileData[0].FileName);
        //    fileData[0].SaveAs(excelFile); //上传到临时目录中
        //    #endregion

        //    #region 读取excel
        //    Workbook book = new Workbook();
        //    book.Open(excelFile);
        //    Worksheet sheet = null;
        //    sheet = book.Worksheets[0];
        //    Cells cells = sheet.Cells;
        //    //获取excel中的数据保存到一个datatable中     
        //    dt = cells.ExportDataTableAsString(0, 0, cells.MaxDataRow + 1, cells.MaxDataColumn + 1, true);
        //    #endregion

        //    File.Delete(excelFile);//删除临时文件

        //    return dt;
        //}

        #region 模板导出
        /// <summary>
        /// 模板导出 （只支持2003-2009版本Excel）
        /// </summary>
        /// <param name="dt">导出数据集</param>
        /// <param name="RowStart">从第几行开始填充数据</param>
        /// <param name="SheetName">Sheet名称</param>
        /// <param name="TemplateName">模板名称带后缀名</param>
        /// <param name="FileName">下载文件名称</param>
        public static void ExcelExportByTemplate(DataTable dt, int RowStart, string SheetName, string TemplateName, string FileName)
        {
            //模板文件  
            string TempletFileName = "~/TemporaryFiles/ExportTemplate/" + TemplateName;
            FileStream file = new FileStream(TempletFileName, FileMode.Open, FileAccess.Read);
            HSSFWorkbook hssfworkbook = new HSSFWorkbook(file);
            HSSFSheet ws = (HSSFSheet)hssfworkbook.GetSheet(SheetName);
            HSSFCellStyle style = (HSSFCellStyle)hssfworkbook.CreateCellStyle();
            HSSFFont font = (HSSFFont)hssfworkbook.CreateFont();
            font.FontHeightInPoints = 11;
            font.FontName = "宋体";
            font.Color = HSSFColor.Black.Index;
            style.SetFont(font);
            style.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
            style.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
            style.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
            style.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
            style.BottomBorderColor = HSSFColor.Black.Index;
            style.LeftBorderColor = HSSFColor.Black.Index;
            style.RightBorderColor = HSSFColor.Black.Index;
            style.TopBorderColor = HSSFColor.Black.Index;

            if (dt.Rows.Count > 0)
            {

                for (int Row = 0; Row < dt.Rows.Count; Row++)
                {
                    HSSFRow dr = (HSSFRow)ws.CreateRow(Row + 2);
                    for (int Col = 0; Col < dt.Columns.Count; Col++)
                    {
                        string ColValue = dt.Rows[Row][Col].ToString();
                        //ws.GetRow(2).GetCell(1).SetCellValue("rrr");
                        HSSFCell ColCell = (HSSFCell)dr.CreateCell(Col);
                        ColCell.CellStyle = style;
                        ColCell.SetCellValue(ColValue);
                    }
                }
            }
            #region 删除Excel文件
            FileHelper Is_files = new FileHelper();
            Is_files.DeleteFile("/TemporaryFiles/temp/" + FileName);
            #endregion
            FileStream files = new FileStream("/TemporaryFiles/temp/" + FileName, FileMode.Create);
            hssfworkbook.Write(files);
            files.Close();
        }

        #endregion

        /// <summary>
        /// 导出
        /// </summary>
        /// <param name="dt">数据原</param>
        /// <param name="ExcelSheetName">sheet名称</param>
        /// <param name="FileName">文件名</param>
        /// <param name="Employee_Code">目录名</param>
        public static void ExcelExportList(List<DataTable> dt, List<string> ExcelSheetName, string FileName, string DirectoryName)
        {
            if (dt[0].Rows.Count > 0)
            {
                int RowCount = dt[0].Rows.Count;
                int SheetMaxCount = 65535;//65535 sheet最大数据行数
                int rows = 0;
                double sheetPage = Convert.ToDouble(Convert.ToDouble(RowCount) / Convert.ToDouble(SheetMaxCount));
                int sheetsCount = Convert.ToInt32(Math.Ceiling(sheetPage));
                IWorkbook hssfworkbook = new HSSFWorkbook();
                IDataFormat format = hssfworkbook.CreateDataFormat();
                int f = 0;
                foreach (var item in dt)
                {
                    for (int k = 0; k < sheetsCount; k++)
                    {
                        string SheetName = ExcelSheetName[f].ToString();
                        hssfworkbook.CreateSheet(SheetName);
                        #region 创建样式对象
                        IFont Font_Size = hssfworkbook.CreateFont();
                        Font_Size.FontHeightInPoints = 10;

                        ICellStyle Style_Font_Size = hssfworkbook.CreateCellStyle();
                        Style_Font_Size.SetFont(Font_Size);
                        Style_Font_Size.WrapText = true;

                        ICellStyle Style_Title = hssfworkbook.CreateCellStyle();
                        Style_Title.FillForegroundColor = HSSFColor.RoyalBlue.Index;
                        //Style_Title.FillPattern = FillPatternType.SQUARES;
                        Style_Title.FillBackgroundColor = HSSFColor.RoyalBlue.Index;

                        IFont Font_Size_Title = hssfworkbook.CreateFont();
                        Font_Size_Title.FontHeightInPoints = 11;
                        Font_Size_Title.Boldweight = (short)FontBoldWeight.Bold;
                        Font_Size_Title.Color = HSSFColor.White.Index;
                        Style_Title.SetFont(Font_Size_Title);
                        Style_Title.SetFont(Font_Size_Title);

                        #endregion

                        #region 创建Excel列头
                        if (dt != null && dt[f].Rows.Count > 0)
                        {
                            hssfworkbook.GetSheet(SheetName).CreateRow(0);
                            for (int a = 0; a < dt[f].Columns.Count; a++)
                            {
                                hssfworkbook.GetSheet(SheetName).GetRow(0).CreateCell(a).CellStyle = Style_Title;
                                hssfworkbook.GetSheet(SheetName).GetRow(0).GetCell(a).SetCellValue(dt[f].Columns[a].ColumnName);
                                hssfworkbook.GetSheet(SheetName).SetColumnWidth(a, 20 * 256);
                            }
                        }
                        #endregion

                        #region 内容填空
                        if (dt != null && dt[f].Rows.Count > 0)
                        {
                            List<string> ToatalList = new List<string>();
                            int counts = 0;

                            for (int i = rows; i < dt[f].Rows.Count; i++)
                            {
                                if (k == 0 && i > SheetMaxCount - 1)
                                {
                                    rows = SheetMaxCount;
                                    break;
                                }
                                else if (k > 0 && i > (SheetMaxCount * (k + 1)) - 1)
                                {
                                    rows = SheetMaxCount * (k + 1);
                                    break;
                                }
                                hssfworkbook.GetSheet(SheetName).CreateRow(counts + 1);
                                for (int j = 0; j < dt[f].Columns.Count; j++)
                                {
                                    hssfworkbook.GetSheet(SheetName).GetRow(counts + 1).CreateCell(j).CellStyle = Style_Font_Size;
                                    hssfworkbook.GetSheet(SheetName).GetRow(counts + 1).GetCell(j).SetCellValue(dt[f].Rows[i][j].ToString());

                                }
                                counts++;
                            }
                        }
                        #endregion
                    }

                    f += 1;
                }

                string SaveFilePath =@"~/TemporaryFiles/" + DirectoryName + @"/";// HttpContext.Current.Server.MapPath("~/TemporaryFiles/" + DirectoryName + "/");
                if (!Directory.Exists(SaveFilePath))//是否存在该路径,不存在创建
                {
                    Directory.CreateDirectory(SaveFilePath);//创建
                }
                #region 删除Excel文件
                FileHelper file = new FileHelper();
                file.DeleteFile(@"~/TemporaryFiles/" + DirectoryName + @"/" + FileName);//HttpContext.Current.Server.MapPath("~/TemporaryFiles/" + DirectoryName + "/" + FileName));
                #endregion
                FileStream files = new FileStream(SaveFilePath + FileName, FileMode.Create);
                hssfworkbook.Write(files);
                files.Close();
                //HttpResponse contextResponse = HttpContext.Current.Response;
                //contextResponse.Redirect(string.Format("~/TemporaryFiles/" + DirectoryName + "/{0}", FileName + ".xls"), false);
            }
        }
    }
}
